
package com.skyc.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.skyc.dao.ClothesDao;
import com.skyc.entity.Clothes;
import com.skyc.util.JDBCUtil;

/**
 *
 * @author BWJ
 * @date 2021年5月15日 下午3:53:30
 */

public class ClothesDaoImpl implements ClothesDao {
	JDBCUtil ju = JDBCUtil.getJDBCUtil();

	@Override
	public Clothes queryByID(String id) {
		String sql = "SELECT * FROM t_clothes WHERE c_id=?";
		CachedRowSet crs = ju.query(sql, id);
		return returnClothes(crs);
	}

	@Override
	public Clothes clothesqueryByCcid(String ccid) {
		String sql = "SELECT * FROM t_clothes WHERE c_cid=? limit 1";
		CachedRowSet crs = ju.query(sql, ccid);
		return returnClothes(crs);
	}
	@Override
	public int cidqueryByIdSizeColor(String ccid, String size, String color) {
		String sql = "select c_id  from t_clothes where c_cid="+ ccid
				+" and c_size='"+size+"' and c_color='"+color+"'";
		CachedRowSet crs = ju.query(sql);
		int i = 0;
		try {
			if (crs.next()) {
				i = crs.getInt("c_id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ju.closeCacheRowSet(crs);
		}
		return i;
	}
	@Override
	public int kucunqueryByIDSizeColor(String ccid, String size, String color) {
		String sql = "select sum(c_stock) as a from t_clothes where c_cid=" + ccid;
		if (!"".equals(size)) {
			sql += " and c_size='" + size + "' ";
		}
		if (!"".equals(color)) {
			sql += " and c_color='" + color + "' ";
		}
		CachedRowSet crs = ju.query(sql);
		int i = 0;
		try {
			if (crs.next()) {
				i = crs.getInt("a");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ju.closeCacheRowSet(crs);
		}
		return i;
	}

	@Override
	public List<Clothes> listqueryBySalesCcid() {
		String sql = "select c_cid, SUM(c_sales) FROM t_clothes GROUP BY c_cid ORDER BY SUM(c_sales) DESC " ;
		CachedRowSet crs = ju.query(sql);
		List<Clothes> list=againqueryByCcid(crs);
		return list;
	}

	@Override
	public int salesqueryByccid(String ccid) {
		String sql = "select  SUM(c_sales) as a FROM t_clothes GROUP BY c_cid having c_cid=?";
		CachedRowSet crs = ju.query(sql, ccid);
		int sales = 0;
		try {
			if (crs.next()) {
				sales = crs.getInt("a");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ju.closeCacheRowSet(crs);
		}
		return sales;
	}
	private String returnSql(){
		return "SELECT * FROM t_clothes WHERE c_sex like ? "
				+ "AND c_type like ? AND c_color like ?  AND c_size like ? " + "AND c_price>=? AND c_price<=?  ";
	}
	@Override
	public List<Clothes> queryTermAll(String sex, String type, String color, String size, String priceMin,
			String priceMax) {
		String sql = returnSql();
		CachedRowSet crs = ju.query(sql, sex, type, color, size, priceMin, priceMax);
		return returnClothess(crs);
	}
	

	@Override
	public List<Clothes> queryPriceAsc(String sex, String type, String color, String size, String priceMin,
			String priceMax,String page,String pagesize) {
		String sql=returnSql()+ " ORDER BY c_price ASC LIMIT "+page+" ,"+pagesize;
		CachedRowSet crs=ju.query(sql, sex, type, color, size, priceMin, priceMax);
		return returnClothess(crs);
	}

	@Override
	public List<Clothes> queryPriceDesc(String sex, String type, String color, String size, String priceMin,
			String priceMax,String page,String pagesize) {
		String sql=returnSql()+ " ORDER BY c_price DESC LIMIT "+page+" ,"+pagesize;
		CachedRowSet crs=ju.query(sql, sex, type, color, size, priceMin, priceMax);
		return returnClothess(crs);
	}

	@Override
	public List<Clothes> querySalesAsc(String sex, String type, String color, String size, String priceMin,
			String priceMax,String page,String pagesize) {
		String sql=returnSql()+ " ORDER BY c_sales ASC LIMIT "+page+" ,"+pagesize;
		CachedRowSet crs=ju.query(sql, sex, type, color, size, priceMin, priceMax);
		return returnClothess(crs);
	}

	@Override
	public List<Clothes> querySalesDesc(String sex, String type, String color, String size, String priceMin,
			String priceMax,String page,String pagesize) {
		String sql=returnSql()+ " ORDER BY c_sales DESC LIMIT "+page+" ,"+pagesize;
		CachedRowSet crs=ju.query(sql, sex, type, color, size, priceMin, priceMax);
		return returnClothess(crs);
	}

	

	// 工具
	public  List<Clothes> againqueryByCcid(CachedRowSet crs) {
		List<Clothes>  list=new ArrayList<Clothes>();
		try {
			while (crs.next()) {
				Clothes c = clothesqueryByCcid(crs.getInt("c_cid")+"");
				list.add(c);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	private Clothes returnClothesUtil(CachedRowSet crs) throws SQLException {
		Clothes c = new Clothes();
		c.setCcolor(crs.getString("c_color"));
		c.setCid(crs.getInt("c_id"));
		c.setCcid(crs.getInt("c_cid"));
		c.setCimgurl(crs.getString("c_imgurl"));
		c.setCmaintype(crs.getString("c_maintype"));
		c.setCname(crs.getString("c_name"));
		c.setCprice(crs.getInt("c_price"));
		c.setCsales(crs.getInt("c_sales"));
		c.setCsex(crs.getString("c_sex"));
		c.setCsize(crs.getString("c_size"));
		c.setCstock(crs.getInt("c_stock"));
		c.setCtype(crs.getString("c_type"));
		return c;
	}

	private Clothes returnClothes(CachedRowSet crs) {
		Clothes c = null;
		try {
			if (crs.next()) {
				c = returnClothesUtil(crs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ju.closeCacheRowSet(crs);
		}
		return c;

	}

	private List<Clothes> returnClothess(CachedRowSet crs) {
		List<Clothes> cs = new ArrayList<Clothes>();
		try {
			while (crs.next()) {
				Clothes c = null;
				c = returnClothesUtil(crs);
				cs.add(c);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			ju.closeCacheRowSet(crs);
		}
		return cs;

	}

	

	

}
